The first step you should complete before actually stepping through this notebook is to install all required dependencies. Assuming you already have Anaconda installed, you'll only need to install TM1py. To do this run the following commands:
pip install TM1py
The code requires an FX cube that contains data for 2017. You can run this script to set up the cube up in your TM1 model. In order to load the exchange rates into the cube run this script .
Before we can start python'ing, we need to import a few modules that are required. TM1py for interacting with the TM1 API, Pandas for data manipulation and transformation, matplotlib for creating plots and xlwings to interact with Excel sheets from python.
In [1]:
from TM1py.Services import TM1Service
from TM1py.Utils import Utils
import pandas as pd
import xlwings as xw
import matplotlib
import matplotlib.pyplot as plt
%matplotlib inline
plt.style.use('ggplot')
In [2]:
# Server address
address = 'localhost'
# HTTP port number - this can be found in your config file
port = '12354'
# username
user = 'admin'
# password
password = 'apple'
# SSL parameter - this can be found in your config file
ssl = True
The following code obtains a data set from TM1 through an MDX Query. The resulting dataset is transformed into a pandas dataframe for statistical analysis.
For more on MDX Queries check out this basic MDX Turoial For more on pandas and dataframes check out this pandas tutorial.
In [3]:
# MDX Query to get the daily exchange Rates from USD to CHF in 2017
mdx = "SELECT \
NON EMPTY {[TM1py Date].[2017-01-01]:[TM1pyDate].[2017-12-31]} ON ROWS, \
{[TM1py Currency To].[CHF], [TM1py Currency To].[EUR], [TM1py Currency To].[GBP]} ON COLUMNS \
FROM [TM1py FX Rates] \
WHERE ([TM1py Currency From].[USD], [TM1py FX Rates Measure].[Spot])"
In [4]:
with TM1Service(address=address, port=port, user=user, password=password, ssl=ssl) as tm1:
# Query data through MDX
data = tm1.cubes.cells.execute_mdx(mdx)
# Transform data into Pandas DataFrame
df = Utils.build_pandas_dataframe_from_cellset(data, multiindex=True)
With Pandas we can do all kind of statistical analysis with our data set. In this Block we calculate statistical measures by currency pair over time.
In [5]:
print(df.std(level='TM1py Currency To'))
In [6]:
print(df.median(level='TM1py Currency To'))
Plot the DataFrame with matplotlib over time.
In [7]:
# Setup the Canvas
plt.rcParams['figure.figsize']=(20,10)
fig = plt.figure()
ax = fig.add_subplot(1,1,1)
# Manipulate DataFrame to simplify plotting
df_plot = df.unstack(level=['TM1py Currency From', 'TM1py Currency To', 'TM1py FX Rates Measure'])
df_plot.index = pd.to_datetime(df_plot.index)
# Draw graph into Canvas
df_plot.plot(legend=False, ax=ax)
Out[7]:
In [8]:
# Rolling 30 days Mean
window = 30
# Setup the Canvas
plt.rcParams['figure.figsize']=(20,10)
fig = plt.figure()
ax = fig.add_subplot(1,1,1)
# Group by currency, calculate and draw graph into plot
for cur, df_cur in df.groupby(level='TM1py Currency To'):
# Manipulate DataFrame to simplify plotting
df_cur = df_cur.unstack(level=['TM1py Currency From', 'TM1py Currency To', 'TM1py FX Rates Measure'])
df_cur.index = pd.to_datetime(df_cur.index)
# Draw graph into Canvas
df_cur.rolling(window=window).mean().plot(legend=False, ax=ax)
Dump the entire dataframe in a csv like format into Excel (starting at cell A1). Requires Excel to be open!
In [9]:
xw.Range('A1').value = df
Dump the plot as an image into your Excel Sheet. Requires Excel to be open!
In [10]:
xw.sheets.active.pictures.add(fig, name="2017 USD to CHF, EUR and GBP")
Out[10]: